insert overwrite table jms_dwd.dwd_wide_abnormal_union_waybill_dt
select
      waybill_no                       as  waybill_no                     --运单号
     ,apply_time                       as  scan_time                      --退转件申请时间
     ,apply_network_code               as  scan_site_code                 --退转件申请网点code
     ,apply_network_name               as  scan_site                      --退转件申请网点名称
     ,cast(apply_type_code as string)  as  type_code                      --退转件申请名称
     ,apply_type_name                  as  type_desc                      --退转件申请类型
     ,'reback'                         as  scan_type                      --扫描类型
     ,status                           as  status                         --状态 1待审核，2已审核    3 取消申请    4.驳回
     ,reback_transfer_reason           as  abnormal_reason                --退转原因
     ,null                             as  input_time                     --录入时间
     ,examine_time                     as  examine_time                   --审核时间
     ,examine_network_code             as  examine_network_code           --审核网点code
     ,examine_network_name             as  examine_network_name           --审核网点名称
     ,print_time                       as  print_time                     --打印时间
     ,print_network_code               as  print_network_code             --打印网点code
     ,print_network_name               as  print_network_name             --打印网点名称
     ,sender_network_code              as  sender_network_code            --寄件网点code
     ,sender_network_name              as  sender_network_name            --寄件网点名称
     ,new_terminal_dispatch_code       as  terminal_dispatch_code         --三段码
     ,receiver_detailed_address        as  receiver_detailed_address      --原收件详细地址
     ,newreceiver_detailed_address     as  newreceiver_detailed_address   --新收件详细地址
     ,transfer_waybill_no              as  transfer_waybill_no            --转寄运单号
     ,order_source_code                as  order_source_code              --订单来源code
     ,null                             as  end_piece_update_time          --完结件更新时间
     ,null                             as  end_piece_is_delete            --完结件是否标记作废 1 是 2 否'
     ,null                             as  end_piece_source               --完结件来源 1、问题件 2、质量工单 3、仲裁 4、客服工单
     ,null                             as  intercept_cancal_time          --拦截件撤销时间
     ,null                             as  intercept_cancal_network_code  --拦截件撤销网点编码
     ,null                             as  intercept_cancal_network_name  --拦截件撤销网点名称
     ,null                             as  intercept_scan_time            --拦截件扫描时间
     ,null                             as  intercept_scan_network_code    --拦截件扫描网点编码
     ,null                             as  intercept_scan_network_name    --拦截件扫描网点名称
     ,null                             as  intercept_registration_time    --拦截件登记时间
     ,terminal_dispatch_code           as  old_terminal_dispatch_code     --退转件原三段码
     ,to_date(apply_time)              as  dt      --分区时间
-- from jms_dwd.dwd_tab_reback_transfer_express_base  --退转件
-- where to_date(apply_time) between date_sub('{{ execution_date | cst_ds }}',7) and '{{ execution_date | cst_ds }}'  --回刷7天
from jms_dwd.dwd_tab_reback_transfer_express_base_dt
where dt between date_sub('{{ execution_date | cst_ds }}',7) and '{{ execution_date | cst_ds }}'  --回刷7天

union all

select
      waybill_no         as  waybill_no
     ,scan_time          as  scan_time                     --问题件扫描时间
     ,scan_site_code     as  scan_site_code                --问题件扫描网点code
     ,scan_site          as  scan_site                     --问题件扫描网点名称
     ,problem_type_code  as  type_code                     --问题件问题类型编码
     ,problem_type       as  type_desc                     --问题件问题类型
     ,'difficult'        as  scan_type                     --扫描类型
     ,null               as  status                        --状态
     ,problem_cause      as  abnormal_reason               --问题件问题原因描述
     ,input_time         as  input_time                    --录入时间
     ,null               as  examine_time                   --审核时间
     ,null               as  examine_network_code           --审核网点code
     ,null               as  examine_network_name           --审核网点名称
     ,null               as  print_time                     --打印时间
     ,null               as  print_network_code             --打印网点code
     ,null               as  print_network_name             --打印网点名称
     ,null               as  sender_network_code            --寄件网点code
     ,null               as  sender_network_name            --寄件网点名称
     ,null               as  terminal_dispatch_code         --三段码
     ,null               as  receiver_detailed_address      --原收件详细地址
     ,null               as  newreceiver_detailed_address   --新收件详细地址
     ,null               as  transfer_waybill_no            --转寄运单号
     ,null               as  order_source_code              --订单来源code
     ,null               as  end_piece_update_time          --完结件更新时间
     ,null               as  end_piece_is_delete            --是否标记作废 1 是 2 否'
     ,null               as  end_piece_source               --完结件来源 1、问题件 2、质量工单 3、仲裁 4、客服工单
     ,null               as  intercept_cancal_time          --拦截件撤销时间
     ,null               as  intercept_cancal_network_code  --拦截件撤销网点编码
     ,null               as  intercept_cancal_network_name  --拦截件撤销网点名称
     ,null               as  intercept_scan_time            --拦截件扫描时间
     ,null               as  intercept_scan_network_code    --拦截件扫描网点编码
     ,null               as  intercept_scan_network_name    --拦截件扫描网点名称
     ,null               as  intercept_registration_time    --拦截件登记时间
     ,null               as  old_terminal_dispatch_code     --退转件原三段码
     ,to_date(scan_time) as  dt
from jms_dwd.dwd_tab_barscan_difficult_base_dt  --问题件
where dt between date_sub('{{ execution_date | cst_ds }}',7) and '{{ execution_date | cst_ds }}'  --回刷7天
  and to_date(scan_time) between date_sub('{{ execution_date | cst_ds }}',7) and '{{ execution_date | cst_ds }}'  --回刷7天

union all

select
      waybill_no        as    waybill_no
     ,end_time          as    scan_time                      --完结件完结时间
     ,network_code      as    scan_site_code                 --完结件扫描网点code
     ,network_name      as    scan_site                      --完结件扫描网点名称
     ,end_code          as    type_code                      --完结类型编码
     ,end_code_desc     as    type_desc                      --完结件编码描述
     ,'end'             as    scan_type                      --扫描类型
     ,null              as    status                         --状态
     ,null              as    abnormal_reason                --完结原因
     ,input_time        as    input_time                     --完结件录入时间
     ,null              as    examine_time                   --审核时间
     ,null              as    examine_network_code           --审核网点code
     ,null              as    examine_network_name           --审核网点名称
     ,null              as    print_time                     --打印时间
     ,null              as    print_network_code             --打印网点code
     ,null              as    print_network_name             --打印网点名称
     ,null              as    sender_network_code            --寄件网点code
     ,null              as    sender_network_name            --寄件网点名称
     ,null              as    terminal_dispatch_code         --三段码
     ,null              as    receiver_detailed_address      --原收件详细地址
     ,null              as    newreceiver_detailed_address   --新收件详细地址
     ,null              as    transfer_waybill_no            --转寄运单号
     ,null              as    order_source_code              --订单来源code
     ,update_time       as    end_piece_update_time          --完结件更新时间
     ,is_delete         as    end_piece_is_delete            --是否标记作废 1 是 2 否'
     ,source            as    end_piece_source               --完结件来源 1、问题件 2、质量工单 3、仲裁 4、客服工单
     ,null              as    intercept_cancal_time          --拦截件撤销时间
     ,null              as    intercept_cancal_network_code  --拦截件撤销网点编码
     ,null              as    intercept_cancal_network_name  --拦截件撤销网点名称
     ,null              as    intercept_scan_time            --拦截件扫描时间
     ,null              as    intercept_scan_network_code    --拦截件扫描网点编码
     ,null              as    intercept_scan_network_name    --拦截件扫描网点名称
     ,null              as    intercept_registration_time    --拦截件登记时间
     ,null              as  old_terminal_dispatch_code       --退转件原三段码
     ,to_date(end_time) as dt
from jms_dwd.dwd_tab_end_piece_base    --完结件表
where to_date(end_time) between date_sub('{{ execution_date | cst_ds }}',7) and '{{ execution_date | cst_ds }}'  --回刷7天

union all

select
      waybill_no               as    waybill_no
     ,intercept_time           as    scan_time                      --拦截件拦截时间
     ,intercept_network_code   as    scan_site_code                 --拦截件拦截网点编码
     ,intercept_network_name   as    scan_site                      --拦截件拦截网点名称
     ,cast(type as string)     as    type_code                      --拦截件类型编号
     ,type_name                as    type_desc                      --拦截件类型名称：1.取消订单，2.错分件，3.问题件
     ,'intercept'              as    scan_type                      --扫描类型
     ,status                   as    status                         --拦截件拦截状态:1待拦截, 2已拦截, 3已撤销
     ,null                     as    abnormal_reason                --拦截原因
     ,null                     as    input_time                     --录入时间
     ,null                     as    examine_time                   --审核时间
     ,null                     as    examine_network_code           --审核网点code
     ,null                     as    examine_network_name           --审核网点名称
     ,null                     as    print_time                     --打印时间
     ,null                     as    print_network_code             --打印网点code
     ,null                     as    print_network_name             --打印网点名称
     ,null                     as    sender_network_code            --寄件网点code
     ,null                     as    sender_network_name            --寄件网点名称
     ,null                     as    terminal_dispatch_code         --三段码
     ,null                     as    receiver_detailed_address      --原收件详细地址
     ,null                     as    newreceiver_detailed_address   --新收件详细地址
     ,null                     as    transfer_waybill_no            --转寄运单号
     ,null                     as    order_source_code              --订单来源code
     ,null                     as    end_piece_update_time          --完结件更新时间
     ,null                     as    end_piece_is_delete            --是否标记作废 1 是 2 否'
     ,null                     as    end_piece_source               --完结件来源 1、问题件 2、质量工单 3、仲裁 4、客服工单
     ,cancal_time              as    intercept_cancal_time          --拦截件撤销时间
     ,cancal_network_code      as    intercept_cancal_network_code  --拦截件撤销网点编码
     ,cancal_network_name      as    intercept_cancal_network_name  --拦截件撤销网点名称
     ,scan_time                as    intercept_scan_time            --拦截件扫描时间
     ,scan_network_code        as    intercept_scan_network_code    --拦截件扫描网点编码
     ,scan_network_name        as    intercept_scan_network_name    --拦截件扫描网点名称
     ,registration_time        as    intercept_registration_time    --拦截件登记时间
     ,null                     as  old_terminal_dispatch_code       --退转件原三段码
     ,to_date(registration_time)  as dt
from jms_dwd.dwd_yl_oms_interceptorpiece_base     --拦截件
where to_date(registration_time) between date_sub('{{ execution_date | cst_ds }}',7) and '{{ execution_date | cst_ds }}'  --回刷7天
    distribute by dt,pmod(hash(rand()),10);

